'''
Created on 2012-7-15

@author: DXD.Spirits
'''

#===============================================================================
# table_list_full = {
#    'agency', 'stops', 'routes', 'trips', 'stop_times', 
#    'calendar', 'calendar_dates', 
#    'fare_attributes', 'fare_rules', 
#    'shapes', 'frequencies', 
#    'transfers', 'feed_info', 
# }
#===============================================================================


global_table_list = {'gtfs_dataset', 'global_graph'}
graph_table_list = {'graph_nodes', 'graph_arcs', 'graph_timetable', 'graph_routes'}
gtfs_table_list = {'agency', 'stops', 'routes', 'trips', 'stop_times', 'calendar', 'calendar_dates'}


table_sql = dict()

"""TODO"""
index_sql = dict()
index_sql_merge = dict()
"""TODO"""


""" ------------------------------ global graph tables ------------------------------ """


table_sql["gtfs_dataset"] = """
CREATE TABLE gtfs_dataset (
    dataset_id    int          NOT NULL AUTO_INCREMENT,
    dataset_name  varchar(50)  NOT NULL,
    dataset_desc  varchar(255) DEFAULT NULL,
    PRIMARY KEY (dataset_id),
    UNIQUE dataset_name(dataset_name)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["global_graph"] = """
CREATE TABLE global_graph (
    arc_id       int  NOT NULL AUTO_INCREMENT,
    dep_dataset  int  NOT NULL,
    dep_node     int  NOT NULL,
    arr_dataset  int  NOT NULL,
    arr_node     int  NOT NULL,
    level        int  NOT NULL,
    cost         int  NOT NULL,
    KEY coupling (dep_dataset, arr_dataset),
    KEY dep_nodes (dep_dataset, dep_node, level),
    KEY arr_nodes (arr_dataset, arr_node, level),
    KEY level (level),
    PRIMARY KEY (arc_id)
) ENGINE=%s DEFAULT CHARSET=utf8
COMMENT='global graph';
"""


""" ------------------------------ gtfs graph tables ------------------------------ """


table_sql["graph_nodes"] = """
CREATE TABLE %s_graph_nodes (
    dataset_id  int          NOT NULL DEFAULT %d,
    node_id     int          NOT NULL AUTO_INCREMENT,
    latitude    int          NOT NULL,
    longitude   int          NOT NULL,
    tile_id     varchar(32)  NOT NULL DEFAULT '',
    KEY tiles (tile_id),
    PRIMARY KEY (dataset_id, node_id)
) ENGINE=%s DEFAULT CHARSET=utf8
COMMENT='graph node map to gtfs stop, with hub level';
"""


table_sql["graph_arcs"] = """
CREATE TABLE %s_graph_arcs (
    dataset_id  int  NOT NULL DEFAULT %d,
    arc_id      int  NOT NULL AUTO_INCREMENT,
    dep_node    int  NOT NULL,
    arr_node    int  NOT NULL,
    route       int  NOT NULL,
    cost        int  NOT NULL,
    KEY routes (dataset_id, route),
    KEY conns (dataset_id, dep_node, arr_node),
    PRIMARY KEY (dataset_id, arc_id)
) ENGINE=%s DEFAULT CHARSET=utf8
COMMENT='stop times with graph route';
"""


table_sql["graph_timetable"] = """
CREATE TABLE %s_graph_timetable (
    dataset_id  int  NOT NULL DEFAULT %d,
    arc_id      int  NOT NULL,
    dep_time    int  NOT NULL,
    arr_time    int  NOT NULL,
    days        int  NOT NULL,
    KEY departures (dataset_id, arc_id, dep_time)
) ENGINE=%s DEFAULT CHARSET=utf8
COMMENT='stop times for the graph';
"""


table_sql["graph_routes"] = """
CREATE TABLE %s_graph_routes (
    dataset_id      int          NOT NULL DEFAULT %d,
    route_id        int          NOT NULL,
    gtfs_route_id   varchar(50)  NOT NULL,
    KEY gtfs_routes (dataset_id, gtfs_route_id),
    PRIMARY KEY (dataset_id, route_id)
) ENGINE=%s DEFAULT CHARSET=utf8
COMMENT='regroup all the trips sharing the same sequence of nodes, walking route_id == 0';
"""


""" ------------------------------ gtfs tables ------------------------------ """


table_sql["stops"] = """
CREATE TABLE %s_stops (
    dataset_id            int           NOT     NULL DEFAULT %d,
    graph_node_id         int           NOT     NULL,
    stop_id               varchar(50)   NOT     NULL,
    stop_code             varchar(50)   DEFAULT NULL,
    stop_name             varchar(50)   DEFAULT NULL,
    stop_desc             varchar(1000) DEFAULT NULL,
    stop_lat              decimal(9,6)  DEFAULT NULL,
    stop_lon              decimal(9,6)  DEFAULT NULL,
    zone_id               varchar(50)   DEFAULT NULL,
    stop_url              varchar(1000) DEFAULT NULL,
    location_type         varchar(50)   DEFAULT NULL,
    parent_station        varchar(50)   DEFAULT NULL,
    stop_timezone         varchar(50)   DEFAULT NULL,
    wheelchair_boarding   varchar(50)   DEFAULT NULL,
    wheelchair_accessible varchar(50)   DEFAULT NULL,
    KEY stop_loc (dataset_id, stop_lat,stop_lon),
    PRIMARY KEY (dataset_id, stop_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["routes"] = """
CREATE TABLE %s_routes (
    dataset_id       int           NOT     NULL DEFAULT %d,
    route_id         varchar(50)   NOT     NULL,
    agency_id        varchar(50)   DEFAULT NULL,
    route_short_name varchar(50)   DEFAULT NULL,
    route_long_name  varchar(1000) DEFAULT NULL,
    route_desc       varchar(1000) DEFAULT NULL,
    route_type       varchar(50)   DEFAULT NULL,
    route_url        varchar(1000) DEFAULT NULL,
    route_color      varchar(50)   DEFAULT NULL,
    route_text_color varchar(50)   DEFAULT NULL,
    -- KEY agency_id (dataset_id, agency_id),
    PRIMARY KEY (dataset_id, route_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["trips"] = """
CREATE TABLE %s_trips (
    dataset_id          int           NOT     NULL DEFAULT %d,
    graph_route_id      int           NOT     NULL,
    route_id            varchar(50)   NOT     NULL,
    service_id          varchar(50)   NOT     NULL,
    trip_id             varchar(50)   NOT     NULL,
    trip_headsign       varchar(1000) DEFAULT NULL,
    trip_short_name     varchar(50)   DEFAULT NULL,
    direction_id        varchar(50)   DEFAULT NULL,
    block_id            varchar(50)   DEFAULT NULL,
    shape_id            varchar(50)   DEFAULT NULL,
    wheelchair_boarding varchar(50)   DEFAULT NULL,
    -- KEY route_id (dataset_id, route_id),
    -- KEY service_id (dataset_id, service_id),
    PRIMARY KEY (dataset_id, trip_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["agency"] = """
CREATE TABLE %s_agency (
    dataset_id      int           NOT     NULL DEFAULT %d,
    agency_id       varchar(50)   DEFAULT NULL,
    agency_name     varchar(50)   DEFAULT NULL,
    agency_url      varchar(1000) DEFAULT NULL,
    agency_timezone varchar(50)   DEFAULT NULL,
    agency_lang     varchar(50)   DEFAULT NULL,
    agency_phone    varchar(50)   DEFAULT NULL,
    agency_fare_url varchar(1000) DEFAULT NULL,
    PRIMARY KEY (dataset_id, agency_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["stop_times"] = """
CREATE TABLE %s_stop_times (
    dataset_id           int         NOT     NULL DEFAULT %d,
    trip_id              varchar(50) NOT     NULL,
    arrival_time         varchar(50) NOT     NULL,
    departure_time       varchar(50) NOT     NULL,
    stop_id              varchar(50) NOT     NULL,
    stop_sequence        int         NOT     NULL,
    stop_headsign        varchar(50) DEFAULT NULL,
    pickup_type          varchar(50) DEFAULT NULL,
    drop_off_type        varchar(50) DEFAULT NULL,
    shape_dist_traveled  varchar(50) DEFAULT NULL,
    -- KEY stop_departure (dataset_id, stop_id, departure_time),
    PRIMARY KEY (dataset_id, trip_id, stop_sequence)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["calendar"] = """
CREATE TABLE %s_calendar (
    dataset_id int          NOT NULL DEFAULT %d,
    service_id varchar(50)  NOT NULL,
    monday     int          NOT NULL,
    tuesday    int          NOT NULL,
    wednesday  int          NOT NULL,
    thursday   int          NOT NULL,
    friday     int          NOT NULL,
    saturday   int          NOT NULL,
    sunday     int          NOT NULL,
    start_date date         NOT NULL,
    end_date   date         NOT NULL,
    PRIMARY KEY (dataset_id, service_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["calendar_dates"] = """
CREATE TABLE %s_calendar_dates (
    dataset_id     int         NOT NULL DEFAULT %d,
    service_id     varchar(50) NOT NULL,
    `date`         date        NOT NULL,
    exception_type varchar(50) NOT NULL,
    PRIMARY KEY service_id_date (dataset_id, service_id, `date`)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["fare_attributes"] = """
CREATE TABLE %s_fare_attributes (
    dataset_id        int         NOT     NULL DEFAULT %d,
    fare_id           varchar(50) DEFAULT NULL,
    price             varchar(50) DEFAULT NULL,
    currency_type     varchar(50) DEFAULT NULL,
    payment_method    varchar(50) DEFAULT NULL,
    transfers         varchar(50) DEFAULT NULL,
    transfer_duration varchar(50) DEFAULT NULL,
    PRIMARY KEY (dataset_id, fare_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["fare_rules"] = """
CREATE TABLE %s_fare_rules (
    dataset_id     int         NOT     NULL DEFAULT %d,
    fare_id        varchar(50) DEFAULT NULL,
    route_id       varchar(50) DEFAULT NULL,
    origin_id      varchar(50) DEFAULT NULL,
    destination_id varchar(50) DEFAULT NULL,
    contains_id    varchar(50) DEFAULT NULL,
    PRIMARY KEY (dataset_id, fare_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["shapes"] = """
CREATE TABLE %s_shapes (
    dataset_id          int         NOT     NULL DEFAULT %d,
    shape_id            varchar(50) DEFAULT NULL,
    shape_pt_lat        varchar(50) DEFAULT NULL,
    shape_pt_lon        varchar(50) DEFAULT NULL,
    shape_pt_sequence   varchar(50) DEFAULT NULL,
    shape_dist_traveled varchar(50) DEFAULT NULL,
    PRIMARY KEY (dataset_id, shape_id, shape_pt_sequence)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["frequencies"] = """
CREATE TABLE %s_frequencies (
    dataset_id   int         NOT     NULL DEFAULT %d,
    trip_id      varchar(50) DEFAULT NULL,
    start_time   varchar(50) DEFAULT NULL,
    end_time     varchar(50) DEFAULT NULL,
    headway_secs varchar(50) DEFAULT NULL,
    exact_times  varchar(50) DEFAULT NULL,
    PRIMARY KEY (dataset_id, trip_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["transfers"] = """
CREATE TABLE %s_transfers (
    dataset_id        int         NOT     NULL DEFAULT %d,
    from_stop_id      varchar(50) DEFAULT NULL,
    to_stop_id        varchar(50) DEFAULT NULL,
    transfer_type     varchar(50) DEFAULT NULL,
    min_transfer_time varchar(50) DEFAULT NULL,
    PRIMARY KEY (dataset_id, from_stop_id, to_stop_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""


table_sql["feed_info"] = """
CREATE TABLE %s_feed_info (
    dataset_id          int         NOT     NULL DEFAULT %d,
    feed_publisher_name varchar(50) DEFAULT NULL,
    feed_publisher_url  varchar(50) DEFAULT NULL,
    feed_lang           varchar(50) DEFAULT NULL,
    feed_start_date     varchar(50) DEFAULT NULL,
    feed_end_date       varchar(50) DEFAULT NULL,
    feed_version        varchar(50) DEFAULT NULL,
    PRIMARY KEY (dataset_id)
) ENGINE=%s DEFAULT CHARSET=utf8;
"""

